﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using ServiceThuVien.LinkModel;
using ServiceThuVien.Model;

namespace ServiceThuVien.SQLServer
{
    public class SQLServerList
    {
        // string conn = ConfigurationSettings.AppSettings["conn"].ToString();
        SqlConnection cn;
        public SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();


        //private static string GetConnectionString()
        //{
        //    // To avoid storing the connection string in your code,
        //    // you can retrieve it from a configuration file. 
        //    return "Server=RADO-PC\\SQLEXPRESS;Integrated Security=SSPI;" +
        //        "Initial Catalog=QLTV";
        //}

        private string GetConnection()
        {
            builder.ConnectionString = "server=RADO-PC\\SQLEXPRESS;user id=sa;" +
            "password= 123;initial catalog=QLTV";
            return builder.ConnectionString;
        }

        private void ConnectSQL(ref SqlConnection cn, string pName, ref SqlCommand cmd)
        {
            cn = new SqlConnection(GetConnection());
            string procName = pName;
            cmd = new SqlCommand();
            cmd.CommandText = procName;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = cn;
        }

        // Get List Doc Gia
        //public DataSet GetListDG()
        //{
        //    string procName = "ListDocGia";

        //    SqlCommand cmd = new SqlCommand();
        //    ConnectSQL(ref cn, procName, ref cmd);
        //    DataSet ds = new DataSet();
        //    try
        //    {
        //        cn.Open();
        //        SqlDataAdapter adap = new SqlDataAdapter(cmd);
        //        adap.Fill(ds);
        //        cn.Close();
        //        return ds;
        //    }
        //    catch
        //    {
        //        return null;
        //    }
        //}

        public List<DSSach> ListThongTinSach()
        {
            string procName = "ListThongTinSach";

            SqlCommand cmd = new SqlCommand();
            ConnectSQL(ref cn, procName, ref cmd);
            DataSet ds = new DataSet();
            List<DSSach> dsThongTinSach = new List<DSSach>();
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSSach temp = new DSSach();

                    temp.MaSach = ds.Tables[0].Rows[i][0].ToString();
                    temp.MaDauSach = ds.Tables[0].Rows[i][1].ToString();
                    temp.TenDauSach = ds.Tables[0].Rows[i][2].ToString();
                    temp.TenTheLoai = ds.Tables[0].Rows[i][3].ToString();
                    temp.HoTenTacGia = ds.Tables[0].Rows[i][4].ToString();
                    temp.NhaXuatBan = ds.Tables[0].Rows[i][5].ToString();
                    temp.NamXuatBan = Int32.Parse(ds.Tables[0].Rows[i][6].ToString());
                    temp.DonGia = Int32.Parse(ds.Tables[0].Rows[i][7].ToString());
                    temp.NgayLap = DateTime.Parse(ds.Tables[0].Rows[i][8].ToString());

                    dsThongTinSach.Add(temp);
                }

                cn.Close();
                return dsThongTinSach;
            }
            catch
            {
                return null;
            }
        }

        public List<DSDocGia> ListDocGia()
        {
            string procName = "ListDocGia";

            SqlCommand cmd = new SqlCommand();
            ConnectSQL(ref cn, procName, ref cmd);
            DataSet ds = new DataSet();
            List<DSDocGia> dsDocGia = new List<DSDocGia>();
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSDocGia temp = new DSDocGia();
                    temp.MaDocGia = ds.Tables[0].Rows[i][0].ToString();
                    temp.HoTenDocGia = ds.Tables[0].Rows[i][1].ToString();
                    temp.TenLoaiDocGia = ds.Tables[0].Rows[i][2].ToString();
                    temp.NgaySinh = DateTime.Parse(ds.Tables[0].Rows[i][3].ToString());
                    temp.DiaChi = ds.Tables[0].Rows[i][4].ToString();
                    temp.Email = ds.Tables[0].Rows[i][5].ToString();
                    temp.NgayLapThe = DateTime.Parse(ds.Tables[0].Rows[i][6].ToString());
                    temp.NgayHetHan = DateTime.Parse(ds.Tables[0].Rows[i][7].ToString());

                    dsDocGia.Add(temp);
                }

                cn.Close();
                return dsDocGia;
            }
            catch
            {
                return null;
            }
        }

        public List<DSPhieuMuon> ListPhieuMuon()
        {
            string procName = "ListPhieuMuon";

            SqlCommand cmd = new SqlCommand();
            ConnectSQL(ref cn, procName, ref cmd);
            DataSet ds = new DataSet();
            List<DSPhieuMuon> dsPhieuMuon = new List<DSPhieuMuon>();
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSPhieuMuon temp = new DSPhieuMuon();

                    temp.MaPhieuMuon = ds.Tables[0].Rows[i][0].ToString();
                    temp.MaDocGia = ds.Tables[0].Rows[i][1].ToString();
                    temp.HoTenDocGia = ds.Tables[0].Rows[i][2].ToString();
                    temp.MaCuonSach = ds.Tables[0].Rows[i][3].ToString();
                    temp.TenDauSach = ds.Tables[0].Rows[i][4].ToString();
                    temp.TenTheLoai = ds.Tables[0].Rows[i][5].ToString();
                    temp.NhaXuatBan = ds.Tables[0].Rows[i][6].ToString();
                    temp.NamXuatBan = Int32.Parse(ds.Tables[0].Rows[i][7].ToString());
                    temp.NgayMuon = DateTime.Parse(ds.Tables[0].Rows[i][8].ToString());
                    temp.NgayPhaiTra = DateTime.Parse(ds.Tables[0].Rows[i][9].ToString());

                    dsPhieuMuon.Add(temp);
                }

                cn.Close();
                return dsPhieuMuon;
            }
            catch
            {
                return null;
            }
        }

        public List<string> ListTenLoaiDocGia()
        {
            string procName = "ListTenLoaiDocGia";
            SqlCommand cmd = new SqlCommand();
            ConnectSQL(ref cn, procName, ref cmd);
            DataSet ds = new DataSet();
            List<string> dsTenLoaiDocGia = new List<string>();
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();
                    dsTenLoaiDocGia.Add(temp);
                }
                cn.Close();
                return dsTenLoaiDocGia;
            }
            catch
            {
                return null;
            }
        }

        public List<LoaiDocGia> ListLoaiDocGia()
        {
            string procName = "ListLoaiDocGia";

            SqlCommand cmd = new SqlCommand();
            ConnectSQL(ref cn, procName, ref cmd);
            DataSet ds = new DataSet();
            List<LoaiDocGia> dsLoaiDocGia = new List<LoaiDocGia>();
            //List<string> dsTenLoaiDocGia = new List<string>();
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    LoaiDocGia temp = new LoaiDocGia();
                    temp.MaLoaiDocGia = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenLoaiDocGia = ds.Tables[0].Rows[i][1].ToString();

                    dsLoaiDocGia.Add(temp);
                }

                cn.Close();
                return dsLoaiDocGia;
            }
            catch
            {
                return null;
            }
        }

        public List<string> ListTenTheLoai()
        {
            string procName = "ListTenTheLoai";
            SqlCommand cmd = new SqlCommand();
            ConnectSQL(ref cn, procName, ref cmd);
            DataSet ds = new DataSet();
            List<string> dsTenTheLoai = new List<string>();
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();
                    dsTenTheLoai.Add(temp);
                }
                cn.Close();
                return dsTenTheLoai;
            }
            catch
            {
                return null;
            }
        }

        public List<string> ListTenDauSach()
        {
            string procName = "ListTenDauSach";
            SqlCommand cmd = new SqlCommand();
            ConnectSQL(ref cn, procName, ref cmd);
            DataSet ds = new DataSet();
            List<string> dsTenDauSach = new List<string>();
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();
                    dsTenDauSach.Add(temp);
                }
                cn.Close();
                return dsTenDauSach;
            }
            catch
            {
                return null;
            }
        }

        public List<string> ListTenTacGia()
        {
            string procName = "ListTenTacGia";
            SqlCommand cmd = new SqlCommand();
            ConnectSQL(ref cn, procName, ref cmd);
            DataSet ds = new DataSet();
            List<string> dsTenTacGia = new List<string>();
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();
                    dsTenTacGia.Add(temp);
                }
                cn.Close();
                return dsTenTacGia;
            }
            catch
            {
                return null;
            }
        }

        //public List<string> ListMaPhieuNhap()
        //{
        //    string procName = "InsertPhieuNhap";
        //    SqlCommand cmd = new SqlCommand();
        //    ConnectSQL(ref cn, procName, ref cmd);
        //    DataSet ds = new DataSet();
        //    List<string> dsMaPhieuNhap = new List<string>();
        //    try
        //    {
        //        cn.Open();
        //        SqlDataAdapter adap = new SqlDataAdapter(cmd);
        //        adap.Fill(ds);
        //        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        //        {
        //            string temp;
        //            temp = ds.Tables[0].Rows[i][0].ToString();
        //            dsMaPhieuNhap.Add(temp);
        //        }
        //        cn.Close();
        //        return dsMaPhieuNhap;
        //    }
        //    catch
        //    {
        //        return null;
        //    }
        //}

        //public int ShowThamSo_KhoangCachNamXB()
        //{
        //    string procName = "ShowThamSo_KhoangCachNamXB";
        //    SqlCommand cmd = new SqlCommand();
        //    ConnectSQL(ref cn, procName, ref cmd);
        //    DataSet ds = new DataSet();
        //    try
        //    {
        //        cn.Open();
        //        SqlDataAdapter adap = new SqlDataAdapter(cmd);
        //        adap.Fill(ds);
        //        int temp;
        //        temp = Int32.Parse(ds.Tables[0].Rows[0][0].ToString());
        //        cn.Close();
        //        return temp;
        //    }
        //    catch
        //    {
        //        return 0;
        //    }
        //}

        //public int ShowThamSo_NgayMuonToiDa()
        //{
        //    string procName = "ShowThamSo_NgayMuonToiDa";
        //    SqlCommand cmd = new SqlCommand();
        //    ConnectSQL(ref cn, procName, ref cmd);
        //    DataSet ds = new DataSet();
        //    try
        //    {
        //        cn.Open();
        //        SqlDataAdapter adap = new SqlDataAdapter(cmd);
        //        adap.Fill(ds);
        //        int temp;
        //        temp = Int32.Parse(ds.Tables[0].Rows[0][0].ToString());
        //        cn.Close();
        //        return temp;
        //    }
        //    catch
        //    {
        //        return 0;
        //    }
        //}

        //public int ShowThamSo_SachMuonToiDa()
        //{
        //    string procName = "ShowThamSo_SachMuonToiDa";
        //    SqlCommand cmd = new SqlCommand();
        //    ConnectSQL(ref cn, procName, ref cmd);
        //    DataSet ds = new DataSet();
        //    try
        //    {
        //        cn.Open();
        //        SqlDataAdapter adap = new SqlDataAdapter(cmd);
        //        adap.Fill(ds);
        //        int temp;
        //        temp = Int32.Parse(ds.Tables[0].Rows[0][0].ToString());
        //        cn.Close();
        //        return temp;
        //    }
        //    catch
        //    {
        //        return 0;
        //    }
        //}

        //public int ShowThamSo_ThoiHanThe()
        //{
        //    string procName = "ShowThamSo_ThoiHanThe";
        //    SqlCommand cmd = new SqlCommand();
        //    ConnectSQL(ref cn, procName, ref cmd);
        //    DataSet ds = new DataSet();
        //    try
        //    {
        //        cn.Open();
        //        SqlDataAdapter adap = new SqlDataAdapter(cmd);
        //        adap.Fill(ds);
        //        int temp;
        //        temp = Int32.Parse(ds.Tables[0].Rows[0][0].ToString());
        //        cn.Close();
        //        return temp;
        //    }
        //    catch
        //    {
        //        return 0;
        //    }
        //}

        //public int ShowThamSo_TienPhat()
        //{
        //    string procName = "ShowThamSo_TienPhat";
        //    SqlCommand cmd = new SqlCommand();
        //    ConnectSQL(ref cn, procName, ref cmd);
        //    DataSet ds = new DataSet();
        //    try
        //    {
        //        cn.Open();
        //        SqlDataAdapter adap = new SqlDataAdapter(cmd);
        //        adap.Fill(ds);
        //        int temp;
        //        temp = Int32.Parse(ds.Tables[0].Rows[0][0].ToString());
        //        cn.Close();
        //        return temp;
        //    }
        //    catch
        //    {
        //        return 0;
        //    }
        //}

        //public int ShowThamSo_TuoiToiThieu()
        //{
        //    string procName = "ShowThamSo_TuoiToiThieu";
        //    SqlCommand cmd = new SqlCommand();
        //    ConnectSQL(ref cn, procName, ref cmd);
        //    DataSet ds = new DataSet();
        //    try
        //    {
        //        cn.Open();
        //        SqlDataAdapter adap = new SqlDataAdapter(cmd);
        //        adap.Fill(ds);
        //        int temp;
        //        temp = Int32.Parse(ds.Tables[0].Rows[0][0].ToString());
        //        cn.Close();
        //        return temp;
        //    }
        //    catch
        //    {
        //        return 0;
        //    }
        //}

        //public int ShowThamSo_TuoiToiDa()
        //{
        //    string procName = "ShowThamSo_TuoiToiDa";
        //    SqlCommand cmd = new SqlCommand();
        //    ConnectSQL(ref cn, procName, ref cmd);
        //    DataSet ds = new DataSet();
        //    try
        //    {
        //        cn.Open();
        //        SqlDataAdapter adap = new SqlDataAdapter(cmd);
        //        adap.Fill(ds);
        //        int temp;
        //        temp = Int32.Parse(ds.Tables[0].Rows[0][0].ToString());
        //        cn.Close();
        //        return temp;
        //    }
        //    catch
        //    {
        //        return 0;
        //    }
        //}

        public List<DSBCMuonSach> ListBCMuonSach()
        {
            string procName = "ListBCMuonSach";

            SqlCommand cmd = new SqlCommand();
            ConnectSQL(ref cn, procName, ref cmd);
            DataSet ds = new DataSet();
            List<DSBCMuonSach> dsPhieuMuon = new List<DSBCMuonSach>();
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSBCMuonSach temp = new DSBCMuonSach();

                    temp.MaTheLoai = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenTheLoai = ds.Tables[0].Rows[i][1].ToString();
                    temp.Thang = Int32.Parse(ds.Tables[0].Rows[i][2].ToString());
                    temp.Nam = Int32.Parse(ds.Tables[0].Rows[i][3].ToString());
                    temp.LuotMuon = Int32.Parse(ds.Tables[0].Rows[i][4].ToString());
                    temp.TongLuotMuon = Int32.Parse(ds.Tables[0].Rows[i][5].ToString());
                    temp.TiLe = float.Parse(ds.Tables[0].Rows[i][6].ToString());

                    dsPhieuMuon.Add(temp);
                }

                cn.Close();
                return dsPhieuMuon;
            }
            catch
            {
                return null;
            }
        }
       
        public List<string> ListMaTheLoai()
        {
            string procName = "ListMaTheLoai";
            SqlCommand cmd = new SqlCommand();
            ConnectSQL(ref cn, procName, ref cmd);
            DataSet ds = new DataSet();
            List<string> dsMaTheLoai = new List<string>();
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();
                    dsMaTheLoai.Add(temp);
                }
                cn.Close();
                return dsMaTheLoai;
            }
            catch
            {
                return null;
            }
        }

        public List<string> ListThamSo()
        {
            string procName = "ListThamSo";
            SqlCommand cmd = new SqlCommand();
            ConnectSQL(ref cn, procName, ref cmd);
            DataSet ds = new DataSet();
            List<string> dsTenThamSo = new List<string>();
           
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();
                    dsTenThamSo.Add(temp);
                }
                cn.Close();
                return dsTenThamSo;
            }
            catch
            {
                return null;
            }

        }

        public List<DSSach> ListSachMatHong(int TinhTrang)
        {
            string procName = "ListSachMatHong";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();

            cmd.Parameters.AddWithValue("@TinhTrang", TinhTrang);

            List<DSSach> dsThongTinSach = new List<DSSach>();
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSSach temp = new DSSach();

                    temp.MaSach = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenDauSach = ds.Tables[0].Rows[i][1].ToString();
                    temp.TenTheLoai = ds.Tables[0].Rows[i][2].ToString();
                    temp.HoTenTacGia = ds.Tables[0].Rows[i][3].ToString();
                    temp.NhaXuatBan = ds.Tables[0].Rows[i][4].ToString();
                    temp.NamXuatBan = Int32.Parse(ds.Tables[0].Rows[i][5].ToString());
                    temp.DonGia = Int32.Parse(ds.Tables[0].Rows[i][6].ToString());

                    dsThongTinSach.Add(temp);
                }

                cn.Close();
                return dsThongTinSach;
            }
            catch
            {
                return null;
            }
        }

        public List<DSNhanVien> ListNhanVien()
        {
            string procName = "ListNhanVien";

            SqlCommand cmd = new SqlCommand();
            ConnectSQL(ref cn, procName, ref cmd);
            DataSet ds = new DataSet();
            List<DSNhanVien> dsNhanVien = new List<DSNhanVien>();
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DSNhanVien temp = new DSNhanVien();

                    temp.MaNhanVien = ds.Tables[0].Rows[i][0].ToString();
                    temp.HoTenNhanVien = ds.Tables[0].Rows[i][1].ToString();
                    temp.MaLoaiNhanVien = ds.Tables[0].Rows[i][2].ToString();
                    temp.TenLoaiNhanVien = ds.Tables[0].Rows[i][3].ToString();

                    dsNhanVien.Add(temp);
                }

                cn.Close();
                return dsNhanVien;
            }
            catch
            {
                return null;
            }
        }

        public List<TheLoai> ListTheLoai()
        {
            string procName = "ListTheLoai";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();

            List<TheLoai> dsTheLoai = new List<TheLoai>();
            try
            {
                cn.Open();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    TheLoai temp = new TheLoai();

                    temp.MaTheLoai = ds.Tables[0].Rows[i][0].ToString();
                    temp.TenTheLoai = ds.Tables[0].Rows[i][1].ToString();
                    
                    dsTheLoai.Add(temp);
                }

                cn.Close();
                return dsTheLoai;
            }
            catch
            {
                return null;
            }
        }
        //public DataSet GetListDG()
        //{
        //    string procName = "ListDocGia";

        //    SqlCommand cmd = new SqlCommand();
        //    ConnectSQL(ref cn, procName, ref cmd);
        //    DataSet ds = new DataSet();
        //    try
        //    {
        //        cn.Open();
        //        SqlDataAdapter adap = new SqlDataAdapter(cmd);
        //        adap.Fill(ds);
        //        cn.Close();
        //        return ds;
        //    }
        //    catch
        //    {
        //        return null;
        //    }
        //}
        public DataSet PrListBCMuonSach()
        {
            string procName = "ListBCMuonSach";

            SqlCommand cmd = new SqlCommand();
            ConnectSQL(ref cn, procName, ref cmd);
            DataSet ds = new DataSet();
            SqlDataAdapter adap = new SqlDataAdapter(cmd);

            try
            {
                cn.Open();
                adap.Fill(ds);
                return ds;
                
            }
            catch
            {
                return null;
            }
            finally
            {
                cn.Close();
                adap.Dispose();
                cn.Dispose();
            }
        }

        public DataSet ExportBCMuonSach(string MaBCMS)
        {
            string procName = "SelectBCMuonSach";

            SqlCommand cmd = new SqlCommand();
            ConnectSQL(ref cn, procName, ref cmd);
            DataSet ds = new DataSet();
            SqlDataAdapter adap = new SqlDataAdapter(cmd);

            cmd.Parameters.AddWithValue("@MaBCMS", MaBCMS);
            try
            {
                cn.Open();
                adap.Fill(ds);
                return ds;

            }
            catch
            {
                return null;
            }
            finally
            {
                cn.Close();
                adap.Dispose();
                cn.Dispose();
            }
        }

        public DataSet ExportBCTraTre(DateTime NgayTra)
        {
            string procName = "SelectBCTraTre";

            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            DataSet ds = new DataSet();
            SqlDataAdapter adap = new SqlDataAdapter(cmd);

            cmd.Parameters.AddWithValue("@NgayTra", NgayTra);
            try
            {
                cn.Open();
                adap.Fill(ds);
                return ds;

            }
            catch
            {
                return null;
            }
            finally
            {
                cn.Close();
                adap.Dispose();
                cn.Dispose();
            }
        }
    }
}